

-- LuP: vymaz udaju "Obsazeno v manipulacnich jednotkach" ve stavech zasob (NESPOUSTET) 
<g>
update a38243u10 set a39911790u10 = null where a39911790u10 is not null
update a234238u10 set a39911794u10 = null where a39911794u10 is not null
update a10201123u10 set a39911792u10 = null where a39911792u10 is not null
update a10201132u10 set a39911796u10 = null where a39911796u10 is not null
delete from a39911815u10
</g>



-- JN (Spustit - Run)
-- change 2167,0 class definition from side B (from 180,0 to 12049999,10)

<g>
if exists(select * from a101u0 where ic = 2167 and u = 0 and ic_b = 180)
begin
  alter table a2167u0 drop constraint c_a2167u0_a180u0b_fk
  update a101u0 set ic_b = 12049999, u_b = 10 where ic = 2167 and u = 0
  <p>
    alter table a2167u0 add constraint c_a2167u0_a12049999u10b_fk foreign key (u_b,ic_b) references a12049999u10 (u,ic)
  </p>
  <a>
    alter table a2167u0 add constraint c_a2167u0_a12049999u10b_fk foreign key (u_b,ic_b,sstate_b) references a12049999u10 (u,ic,sstate)
  </a>
end
</g>

-- transfer all attributes from class 180,0 to 12049999,10
<g>
create procedure dbo.ChangeAttrClass(@AttrIC bigint, @AttrU int, @OClassIC bigint, @OClassU int, @NClassIC bigint, @NClassU int) as
begin
  if exists(select * from a103u0 where (ic_a = @OClassIC) and (u_a = @OClassU) and (ic_b = @AttrIC) and (u_b = @AttrU))
  begin
	declare @attrname nvarchar(50)
	set @attrname = 'a' + convert(nvarchar(30),@AttrIC) + 'u' + convert(nvarchar(20),@AttrU)
	declare @oclassname nvarchar(50)
	set @oclassname = 'a' + convert(nvarchar(30),@OClassIC) + 'u' + convert(nvarchar(20),@OClassU)
	declare @nclassname nvarchar(50)
	set @nclassname = 'a' + convert(nvarchar(30),@NClassIC) + 'u' + convert(nvarchar(20),@NClassU)

	declare @attrtype smallint
	declare @attrprec smallint
	declare @attrcollation nvarchar(50)
	declare @islangattr bit
	declare @langsuffix nvarchar(100)
	declare @fullattrname nvarchar(100)
	declare @attrtypename nvarchar(100)

	select @islangattr = case when a222u0 in (2,9,13) then 1 else 0 end from a102u0 where ic = @AttrIC and u = @AttrU

	if (@islangattr = 1)
	begin
	    declare langs scroll cursor for select 'l' + convert(nvarchar(30),ic) + 'u' + convert(nvarchar(20),u) from a194u0
		open langs
		fetch first from langs into @langsuffix
		while (@@fetch_status = 0)
		begin
		  set @fullattrname = @attrname + @langsuffix

		  select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @fullattrname		  
		  if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
		  if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
		  if (@attrtype = 48) set @attrtypename = 'tinyint' else 
		  if (@attrtype = 52) set @attrtypename = 'smallint' else
		  if (@attrtype = 56) set @attrtypename = 'integer' else
		  if (@attrtype = 127) set @attrtypename = 'bigint' else
		  if (@attrtype = 62) set @attrtypename = 'float' else
		  if (@attrtype = 61) set @attrtypename = 'datetime' else
		  if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

		if (@attrcollation is not null) 
		  exec('alter table ' + @nclassname + ' add ' + @fullattrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
		  exec('alter table ' + @nclassname + ' add ' + @fullattrname + ' ' + @attrtypename)
		exec('update a set ' + @fullattrname + ' = b.' + @fullattrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
		if exists(select * from sysindexes where name = 'i_' + @fullattrname)
		begin
		  exec('drop index ' + @oclassname + '.i_' + @fullattrname)
		  exec('create index i_' + @fullattrname + ' on ' + @nclassname + '(' + @fullattrname + ')')
		end
		exec('alter table ' + @oclassname + ' drop column ' + @fullattrname)  
		  fetch next from langs into @langsuffix
        end
        close langs
        deallocate langs
	end
	else
	begin	
		select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @attrname
		
		if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
		if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
		if (@attrtype = 48) set @attrtypename = 'tinyint' else 
		if (@attrtype = 52) set @attrtypename = 'smallint' else
		if (@attrtype = 56) set @attrtypename = 'integer' else
		if (@attrtype = 127) set @attrtypename = 'bigint' else
		if (@attrtype = 62) set @attrtypename = 'float' else
		if (@attrtype = 61) set @attrtypename = 'datetime' else
		if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

		if (@attrcollation is not null) 
		  exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
		  exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename)
		exec('update a set ' + @attrname + ' = b.' + @attrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
		if exists(select * from sysindexes where name = 'i_' + @attrname)
		begin
		  exec('drop index ' + @oclassname + '.i_' + @attrname)
		  exec('create index i_' + @attrname + ' on ' + @nclassname + '(' + @attrname + ')')
		end
		exec('alter table ' + @oclassname + ' drop column ' + @attrname)  
    end

	update a103u0 set ic_a = @NClassIC, u_a = @NClassU where ic_b = @AttrIC and u_b = @AttrU
  end
end
</g>

<g>
if (not exists(select * from a103u0 where ic_a = 180 and u_a = 0 and ic_b = 362560))
begin
  exec ChangeAttrClass 362560,10,180,0,12049999,10;
  exec ChangeAttrClass 106461,10,180,0,12049999,10;
  exec ChangeAttrClass 5570716,10,180,0,12049999,10;
  exec ChangeAttrClass 393790,10,180,0,12049999,10;
  exec ChangeAttrClass 393830,10,180,0,12049999,10;
  exec ChangeAttrClass 1014194,10,180,0,12049999,10;
  exec ChangeAttrClass 276,0,180,0,12049999,10;
  exec ChangeAttrClass 826360,10,180,0,12049999,10;
  exec ChangeAttrClass 3726284,10,180,0,12049999,10;
  exec ChangeAttrClass 3726286,10,180,0,12049999,10;
  exec ChangeAttrClass 3726282,10,180,0,12049999,10;
end
</g>

-- MiK (component definition change for QI2) run
<g>
if (not exists(select * from a103u0 where ic_a = 29723580 and u_a = 0 and ic_b = 29725918))
begin
  exec ChangeAttrClass 29725918,10,29723580,10,40204825,10;
  exec ChangeAttrClass 29725953,10,29723580,10,40204825,10;
  exec ChangeAttrClass 29725955,10,29723580,10,40204825,10;
  exec ChangeAttrClass 29725957,10,29723580,10,40204825,10;
  exec ChangeAttrClass 29725925,10,29723580,10,40204825,10;
  exec ChangeAttrClass 39461163,10,29725959,10,40204829,10;
end
</g>

<g>
drop procedure ChangeAttrClass
</g>


-- MP (Spustit - run)

<g>
-- not support system attributes (exception is missing)
create procedure dbo.ChangeAttrClass(@AttrIC bigint, @AttrU int, @OClassIC bigint, @OClassU int, @NClassIC bigint, @NClassU int) as
begin
  if exists(select * from a103u0 where (ic_a = @OClassIC) and (u_a = @OClassU) and (ic_b = @AttrIC) and (u_b = @AttrU))
  begin
       declare @attrname nvarchar(50)
       set @attrname = 'a' + convert(nvarchar(30),@AttrIC) + 'u' + convert(nvarchar(20),@AttrU)
       declare @oclassname nvarchar(50)
       set @oclassname = 'a' + convert(nvarchar(30),@OClassIC) + 'u' + convert(nvarchar(20),@OClassU)
       declare @nclassname nvarchar(50)
       set @nclassname = 'a' + convert(nvarchar(30),@NClassIC) + 'u' + convert(nvarchar(20),@NClassU)

       declare @attrtype smallint
       declare @attrprec smallint
       declare @attrcollation nvarchar(50)
       declare @islangattr bit
       declare @langsuffix nvarchar(100)
       declare @fullattrname nvarchar(100)
       declare @attrtypename nvarchar(100)

       select @islangattr = case when a222u0 in (2,9,13) then 1 else 0 end from a102u0 where ic = @AttrIC and u = @AttrU

       if (@islangattr = 1)
       begin
           declare langs scroll cursor for select 'l' + convert(nvarchar(30),ic) + 'u' + convert(nvarchar(20),u) from a194u0
             open langs
             fetch first from langs into @langsuffix
             while (@@fetch_status = 0)
             begin
               set @fullattrname = @attrname + @langsuffix

               select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @fullattrname            
               if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
               if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
               if (@attrtype = 48) set @attrtypename = 'tinyint' else 
               if (@attrtype = 52) set @attrtypename = 'smallint' else
               if (@attrtype = 56) set @attrtypename = 'integer' else
               if (@attrtype = 127) set @attrtypename = 'bigint' else
               if (@attrtype = 62) set @attrtypename = 'float' else
               if (@attrtype = 61) set @attrtypename = 'datetime' else
               if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

             if (@attrcollation is not null) 
               exec('alter table ' + @nclassname + ' add ' + @fullattrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
               exec('alter table ' + @nclassname + ' add ' + @fullattrname + ' ' + @attrtypename)
             exec('update a set ' + @fullattrname + ' = b.' + @fullattrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
             if exists(select * from sysindexes where name = 'i_' + @fullattrname)
             begin
               exec('drop index ' + @oclassname + '.i_' + @fullattrname)
               exec('create index i_' + @fullattrname + ' on ' + @nclassname + '(' + @fullattrname + ')')
             end
             exec('alter table ' + @oclassname + ' drop column ' + @fullattrname)  
               fetch next from langs into @langsuffix
        end
        close langs
        deallocate langs
       end
       else
       begin  
             select @attrtype = xusertype,@attrprec = prec,@attrcollation = collation from syscolumns where name = @attrname
             
             if (@attrtype = 231) and (@attrprec = -1) set @attrtypename = 'nvarchar(max)' else
             if (@attrtype = 231) and (@attrprec <> -1) set @attrtypename = 'nvarchar(' + convert(nvarchar(10),@attrprec) + ')' else
             if (@attrtype = 48) set @attrtypename = 'tinyint' else 
             if (@attrtype = 52) set @attrtypename = 'smallint' else
             if (@attrtype = 56) set @attrtypename = 'integer' else
             if (@attrtype = 127) set @attrtypename = 'bigint' else
             if (@attrtype = 62) set @attrtypename = 'float' else
             if (@attrtype = 61) set @attrtypename = 'datetime' else
             if (@attrtype = 165) set @attrtypename = 'varbinary(max)'

             if (@attrcollation is not null) 
               exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename + ' collate ' + @attrcollation) else
               exec('alter table ' + @nclassname + ' add ' + @attrname + ' ' + @attrtypename)
             exec('update a set ' + @attrname + ' = b.' + @attrname + ' from ' + @nclassname + ' as a join ' + @oclassname + ' as b on (a.ic = b.ic) and (a.u = b.u)')
             if exists(select * from sysindexes where name = 'i_' + @attrname)
             begin
               exec('drop index ' + @oclassname + '.i_' + @attrname)
               exec('create index i_' + @attrname + ' on ' + @nclassname + '(' + @attrname + ')')
             end
             exec('alter table ' + @oclassname + ' drop column ' + @attrname)  
    end

       update a103u0 set ic_a = @NClassIC, u_a = @NClassU where ic_b = @AttrIC and u_b = @AttrU
  end
end
</g>

<g>
exec ChangeAttrClass 12640984,10, 6880098,10, 6880099,10;
</g>
<g>
drop procedure ChangeAttrClass
</g>

-- LG Change speed optimization attribute (is in cache) -- run
<g>
update a102u0 set a229u0 = '0,1,2', a230u0l401u0 = 'Legacy Cardinality Estimator,Recompile,Single Thread', langstate = 3, useric = 1105, useru = 0, ts0 = getdate(), ts1 = getdate() where ic = 5111 and u = 0
</g>





